package jsyf.oa.dao.report.newAppReport;

import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@SuppressWarnings("all")
@Repository
public class NewAppReportForRMDao extends BaseDao {

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    /*区域经理意向客户*/
    public List<JSONObject> getForPersonalIntentCustomerList(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_wrt_customer.`NAME` AS  name,\n" +
                "t_wrt_customer.`ID` AS  id,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.SOURCE AS source,\n" +
                "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT as customerCredit,\n" +
                "t_wrt_customer.PROVINCE_NAME AS provinceName,\n" +
                "t_wrt_customer.CITY_NAME AS cityName,\n" +
                "t_wrt_customer.COUNTY_NAME AS countyName,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.THROW_REASON AS throwReason,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_wrt_customer.INTENTION_TIME AS intentionTime,\n" +
                "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "WHERE 1=1" + "\n");
        sql.append(" AND CREATE_USER_ID=" + "'" + userId + "'" + "\n");
        sql.append("AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n");
        sql.append("GROUP BY\n" +
                "t_wrt_customer.`NAME`,\n" +
                "t_wrt_customer.TEL,\n" +
                "t_wrt_customer.SOURCE ,\n" +
                "t_wrt_customer.INTENTION_MODEL ,\n" +
                "t_wrt_customer.BUY_DATE ,\n" +
                "t_wrt_customer.BUY_TYPE ,\n" +
                "t_wrt_customer.EXISTING_DEVICE,\n" +
                "t_wrt_customer.FUND_CONDITION ,\n" +
                "t_wrt_customer.OTHER_CONDITION" + "\n");
        sql.append("ORDER BY t_wrt_customer.CREATE_TIME DESC ");
        List<JSONObject> records = super.queryForJsonList(sql.toString());
        return records;
    }

    public JSONObject getSignPersonByToday(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\tt_sys_user.name AS userName,\n" +
                "\tt_sys_user.RESIDENCE AS residence,\n" +
                "\tSIGN_URL AS signUrl,\n" +
                "\tt_sys_sign_in.ADDRESS AS address,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_ADDRESS AS signOutAddress,\n" +
                "\tt_sys_sign_in.SIGN_OUT_ALWAYS_DISTANCE AS signOutAlwaysDistance,\n" +
                "\tt_sys_sign_in.SIGN_IN_ALWAYS_DISTANCE AS signInAlwaysDistance,\n" +
                "\tSIGN_OUT_URL AS signOutUrl,\n" +
                "\tt_sys_sign_in.LONGITUDE AS longitude,\n" +
                "\tt_sys_sign_in.LATITUDE AS latitude,\n" +
                "\tt_sys_role_name.FRONT_FLAG AS frontFlag,\n" +
                "\tSUBSTRING(t_sys_sign_in.SIGN_OUT_TIME,12,5) AS signOutTime,\n" +
                "SUBSTRING(t_sys_sign_in.CREATE_TIME,12,5) AS signCreateTime\n" +
                "\n" +
                "FROM\n" +
                "\tt_sys_sign_in\n" +
                "LEFT JOIN t_sys_user ON t_sys_sign_in.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_sign_in.USER_ID='" + userId + "'\n" +
                "AND t_sys_sign_in.SIGN_DATE='" + dateTime + "'");
        return super.queryForJsonObject(sb.toString());
    }

    public JSONObject getLeavePersonByToday(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\tt_sys_user.name AS userName,\n" +
                " LEFT (t_sys_leave.BEGIN_TIME,10) as beginTime ,\n" +
                "LEFT (t_sys_leave.END_TIME,10) as endTime,\n" +
                "\tt_sys_leave.REMARK AS remark\n" +
                "FROM\n" +
                "\tt_sys_leave\n" +
                "LEFT JOIN t_sys_user ON t_sys_leave.USER_ID=t_sys_user.ID\n" +
                "WHERE\n" +
                "1=1\n" +
                "AND \n" +
                "USER_ID='" + userId + "'\n" +
                "AND LEAVE_DATE='" + dateTime + "'");
        return super.queryForJsonObject(sb.toString());
    }

    public List<JSONObject> getForPersonalCostByUserIdAndDateTime(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "  t_sys_user.ID AS userId,\n" +
                "  t_sys_user.`NAME` AS userName,\n" +
                "\tt_wrt_cost.CREATE_USER_ID AS createUserId,\n" +
                "\tt_wrt_cost.CREATE_TIME as createTime,\n" +
                "\tt_wrt_cost.COST_SMALL_NAME AS costSmallName,\n" +
                "\tt_wrt_cost.INVOICE_FLAG AS invoiceFlag,\n" +
                "\tt_wrt_cost.`START` AS bxState,\n" +
                "\tt_wrt_cost.REPORT_DATE AS date,\n" +
                "\tIFNULL(`t_wrt_cost`.`MONEY`, 0) AS `money`,\n" +
                "\tt_wrt_cost_detail.MILEAGE AS mileage,\n" +
                "\tt_wrt_cost_detail.IMG AS img,\n" +
                "\tt_wrt_cost_detail.LICENSE as license,\n" +
                "\tt_wrt_cost_detail.HOTEL_NAME AS hotelName,\n" +
                "\tt_wrt_cost_detail.HOTEL_TEL AS hotelTel,\n" +
                "\tt_wrt_cost_detail.RESIDENCE AS residence,\n" +
                "\tt_wrt_cost_detail.`HOST` AS hostUser,\n" +
                "\tt_wrt_cost_detail.COHABITATION AS cohabitation,\n" +
                "\tt_wrt_cost_detail.GUESTS AS guests,\n" +
                "\tt_wrt_cost_detail.GUEST_NATURE AS guestNature,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_NAME AS restaurantName,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_TEL AS restaurantTel,\n" +
                "\tt_wrt_cost_detail.ENTERTAINMENT_VENUE AS entertainmentVenue,\n" +
                "\tt_wrt_cost_detail.PHONE AS phone,\n" +
                "\tt_wrt_cost_detail.BEFORE_OFFICE AS beforeOffice,\n" +
                "\tt_wrt_cost_detail.AFTER_OFFICE AS afterOffice,\n" +
                "\tt_wrt_cost_detail.OFFICE_MATTERS AS officemetters,\n" +
                "\tt_wrt_cost_detail.PREDICT_MONEY AS predictMoney,\n" +
                "\tt_wrt_cost.REMARK AS remark\n" +
                "FROM t_wrt_cost\n" +
                "LEFT JOIN t_wrt_cost_detail ON t_wrt_cost.ID = t_wrt_cost_detail.FK_COST_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_cost.CREATE_USER_ID\n" +
                "WHERE t_wrt_cost.CREATE_USER_ID = '" + userId + "'\n" +
                "AND t_wrt_cost.REPORT_DATE = '" + dateTime + "'\n" +
                "ORDER BY t_wrt_cost.CREATE_TIME");
        return queryForJsonList(sql.toString());
    }

    //报表--今日数据
    public List<JSONObject> getForPersonalDayByUserIdAndDateTime(String userId, String dateTime, Integer type, Integer status, StringBuffer condition, Integer requestPage, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT customer.* FROM(\n" +
                "SELECT t_wrt_customer.`NAME` as `customerName`\n" +
                ",t_wrt_customer.ID as customerId\n" +
                ",t_wrt_customer.TEL as customerTel\n" +
                ",t_wrt_customer.WECHAT as customerWechat\n" +
                ",t_wrt_customer_visit.ENTOURAGE as entourageName\n" +
                ",t_wrt_customer_visit.ENTOURAGE_ID as entourageId\n" +
                ",t_wrt_customer_visit.CUSTOMER_TYPE as customerType\n" +
                ",t_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit\n" +
                ",t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime\n" +
                ",t_wrt_customer_visit.LEAVE_TIME AS leaveTime\n" +
                ",t_wrt_customer_visit.LEAVE_IMG as leaveImg\n" +
                ",t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg\n" +
                ",t_wrt_customer_visit.OTHER_CONDITION as otherCondition\n" +
                ",substring(t_wrt_customer_visit.ARRIVALS_TIME,11,3)>=12 as status\n" +
                ",IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(t_wrt_customer_visit.LEAVE_TIME)-UNIX_TIMESTAMP(t_wrt_customer_visit.ARRIVALS_TIME))/60),0) as time \n" +
                "FROM t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=" + type + "\n" +
                "AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "'\n");
        if (null != condition) {
            sql.append(condition);
        }
        sql.append("AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "') AS customer\n");
        if (type == 1) {
            sql.append("WHERE customer.status=" + status + "\n");
        }
        sql.append("ORDER BY customer.arrivalsTime\n");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        return jsonList;
    }

    public Integer getForPersonalDayByUserIdAndDateTimePageCount(String userId, String dateTime, Integer type, Integer status, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from(  SELECT customer.* FROM(\n" +
                "SELECT t_wrt_customer.`NAME` as `customerName`\n" +
                ",t_wrt_customer.ID as customerId\n" +
                ",t_wrt_customer.TEL as customerTel\n" +
                ",t_wrt_customer.WECHAT as customerWechat\n" +
                ",t_wrt_customer_visit.ENTOURAGE as entourageName\n" +
                ",t_wrt_customer_visit.ENTOURAGE_ID as entourageId\n" +
                ",t_wrt_customer_visit.CUSTOMER_TYPE as customerType\n" +
                ",t_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit\n" +
                ",t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime\n" +
                ",t_wrt_customer_visit.LEAVE_TIME AS leaveTime\n" +
                ",t_wrt_customer_visit.LEAVE_IMG as leaveImg\n" +
                ",t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg\n" +
                ",t_wrt_customer_visit.OTHER_CONDITION as otherCondition\n" +
                ",substring(t_wrt_customer_visit.ARRIVALS_TIME,11,3)>=12 as status\n" +
                ",IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(t_wrt_customer_visit.LEAVE_TIME)-UNIX_TIMESTAMP(t_wrt_customer_visit.ARRIVALS_TIME))/60),0) as time \n" +
                "FROM t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=" + type + "\n" +
                "AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "'\n");
        if (null != condition) {
            sql.append(condition);
        }
        sql.append("AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "') AS customer\n");
        if (type == 1) {
            sql.append("WHERE customer.status=" + status + "\n");
        }
        sql.append(") as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }

    public List<JSONObject> getTomorrowVisitToday(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.ENTOURAGE AS entourage,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.VISIT_GOAL AS visitGoal,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.CUSTOMER_NAME AS customerName,\n" +
                "\tT_WRT_CUSTOMER_VIST_PLAN.COUNTY AS county,\n" +
                "\tCASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t0\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t3\n" +
                "ELSE\n" +
                "\t2\n" +
                "END AS signState,\n" +
                "CASE WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "0\n" +
                "END as reportState,\n"+
                " CASE\n" +
                "WHEN t_sys_sign_in.ID IS NOT NULL THEN\n" +
                "\t10\n" +
                "WHEN t_sys_leave.ID IS NOT NULL THEN\n" +
                "\t20\n" +
                "WHEN t_sys_email.ID IS NOT NULL THEN\n" +
                "\t30\n" +
                "ELSE\n" +
                "\t40\n" +
                "END signSort\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_company.SORT_NO AS companySortNo,\n" +
                "\t\t\tt_sys_depart.SORT_NO AS departSortNo\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user.ID  = '" + userId + "'\n" +
                "\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN T_WRT_CUSTOMER_VIST_PLAN ON sysUser.userId = T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.CREATE_USER_ID = '" + userId + "'\n" +
                "AND T_WRT_CUSTOMER_VIST_PLAN.REPORT_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_sign_in ON t_sys_sign_in.USER_ID = sysUser.userId\n" +
                "AND t_sys_sign_in.SIGN_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_leave ON t_sys_leave.USER_ID = sysUser.userId\n" +
                "AND t_sys_leave.LEAVE_DATE = '" + dateTime + "'\n" +
                "LEFT JOIN t_sys_email ON t_sys_email.CREATE_USER_ID = sysUser.userId\n" +
                "AND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                "AND t_sys_email.E_TYPE = 1\n" +
                "ORDER BY\n" +
                "\tsysUser.companySortNo,\n" +
                "\tsysUser.departSortNo,\n" +
                "\tsignSort,\n" +
                "sysUser.userId\n");
        return super.queryForJsonList(sql.toString());
    }

    public JSONObject getFirstVisitAndSignForToday(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tsysUser.userId,\n" +
                "\tsysSign.signInLatitude,\n" +
                "\tsysSign.signInLongitude,\n" +
                "\tsysSign.signInspeed,\n" +
                "\tsysSign.signInDiff,\n" +
                "\tsysSign.signTime,\n" +
                "\tsysSign.signInDistance,\n" +
                "\twrtCustomerVisit.firstVisitTime,\n" +
                "\twrtCustomerVisit.arrivalsLatitude,\n" +
                "\twrtCustomerVisit.arrivalsLongitude\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS userId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tid = '" + userId + "'\n" +
                "\t) AS sysUser\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_sign_in.LATITUDE AS signInLatitude,\n" +
                "\t\tt_sys_sign_in.LONGITUDE AS signInLongitude,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_SPEED AS signInspeed,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_TIME_DIFF AS signInDiff,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS signTime,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_DISTANCE AS signInDistance,\n" +
                "\t\tt_sys_sign_in.CREATE_USER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND LEFT (\n" +
                "\t\tt_sys_sign_in.CREATE_TIME,\n" +
                "\t\t10\n" +
                "\t) = '" + dateTime + "'\n" +
                "\tAND t_sys_sign_in.CREATE_USER_ID = '" + userId + "'\n" +
                ") AS sysSign ON sysSign.userId = sysUser.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.ARRIVALS_LATITUDE AS arrivalsLatitude,\n" +
                "\t\tt_wrt_customer_visit.ARRIVALS_LONGITUDE AS arrivalsLongitude,\n" +
                "\t\tmin(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t) AS firstVisitTime,\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tLEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS wrtCustomerVisit ON wrtCustomerVisit.userId = sysSign.userId\n");
        super.appendPageSql(sql, 1, 1);
        return super.queryForJsonObject(sql.toString());
    }

    public List<JSONObject> getVisitCustomerByUserIdAndDateTime(String userId, String dateTime, Integer type) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT shouldDate.date, \n" +
                "IFNULL(visit.num,0) as num\n" +
                ",visit.userId\n" +
                "FROM(\n" +
                "SELECT t_sys_should_holiday.SHOULD_DATE as date\n" +
                "FROM t_sys_should_holiday\n" +
                "WHERE left(t_sys_should_holiday.SHOULD_DATE,7)='" + dateTime + "'\n" +
                "GROUP BY t_sys_should_holiday.SHOULD_DATE) as shouldDate\n" +
                "LEFT JOIN (\n" +
                "SELECT COUNT(1) as num\n" +
                ",t_wrt_customer_visit.REPORT_DATE as date\n" +
                ",t_wrt_customer_visit.CREATE_USER_ID as userId\n" +
                "FROM t_wrt_customer_visit\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE=" + type + "\n" +
                "AND left(t_wrt_customer_visit.REPORT_DATE,7)='" + dateTime + "'\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "'\n" +
                "GROUP BY  t_wrt_customer_visit.REPORT_DATE\n" +
                ") as visit ON visit.date=shouldDate.date\n" +
                "order by shouldDate.date desc\n");
        return super.queryForJsonList(sql.toString());
    }

    public JSONObject getCustomerNumByuserIdAndDateTime(String userId, String dateTime, Integer type) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT newNum.newNum,sunNum.sunNum FROM (\n" +
                "\n" +
                "SELECT\n" +
                "COUNT(1) as newNum ,newNum.userId\n" +
                "FROM (\n" +
                "SELECT\n" +
                "\tt_wrt_customer_visit.CUSTOMER_ID,\n" +
                "\tt_wrt_customer_visit.CREATE_USER_ID AS userId\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_wrt_customer_visit.visit_mode=" + type + "\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n" +
                "AND LEFT (\n" +
                "\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t7\n" +
                ") = '" + dateTime + "'\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID NOT IN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "AND t_wrt_customer_visit.visit_mode=" + type + "\n" +
                "\tAND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n" +
                "\tAND LEFT (\n" +
                "\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t7\n" +
                "\t) != '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                ")\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "GROUP BY\n" +
                "\tt_wrt_customer_visit.CUSTOMER_ID) AS newNum) as newNum\n" +
                "LEFT JOIN (\n" +
                "SELECT COUNT(1) as  sunNum,sunNum.userId\n" +
                "FROM (\n" +
                "SELECT t_wrt_customer_visit.CUSTOMER_ID as customerId\n" +
                ",t_wrt_customer_visit.CREATE_USER_ID as userId\n" +
                "FROM t_wrt_customer_visit\n" +
                "WHERE 1=1\n" +
                "AND t_wrt_customer_visit.visit_mode=" + type + "\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "'\n" +
                "AND t_wrt_customer_visit.CUSTOMER_ID is not null\n" +
                "GROUP BY t_wrt_customer_visit.CUSTOMER_ID) as sunNum\n" +
                ") as sunNum ON sunNum.userId=newNum.userId\n");
        return super.queryForJsonObject(sql.toString());
    }

    public List<JSONObject> getNewSaleManagerList(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("  SELECT\n" +
                "\tt_sys_user.`NAME` as userName,\n" +
                "t_sys_user.ID as userId,\n" +
                "CONCAT(t_sys_region_achivements.BEGIN_MONTH,'-',t_sys_region_achivements.END_MONTH) AS cycle,\n" +
                " IFNULL(t_sys_bonus.SALE_TARGET_NUM,0) as targetNum,\n" +
                " IFNULL(t_sys_bonus.SALE_FINISH_NUM,0) as finishNum,\n" +
                "  IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t`t_sys_bonus` \n" +
                "LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region_achivements ON t_sys_region_achivements.USER_ID=t_sys_bonus.USER_ID\n" +
                "AND t_sys_region_achivements.AREA_ID=t_sys_bonus.BIG_REGION_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_bonus.USER_ID='" + userId + "'\n" +
                "AND t_sys_bonus.TYPE=1 AND left(day,7)='" + dateTime + "'\n" +
                "GROUP BY t_sys_bonus.USER_ID");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getTodayWechatShareList(String dateTime, String userId, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_should_holiday.SHOULD_DATE AS date,\n" +
                "\tIFNULL(\n" +
                "\t\tt_wechat_friends_contacts.CONTACTS,\n" +
                "\t\t0\n" +
                "\t) AS contacts,\n" +
                "\tCOUNT(\n" +
                "\t\tt_sys_wechat_share_detail.ID\n" +
                "\t) AS finishForward,\n" +
                "\tCOUNT(\n" +
                "\t\tt_sys_wechat_share.ID\n" +
                "\t) AS forward\n" +
                "FROM\n" +
                "\tt_sys_should_holiday\n" +
                "LEFT JOIN t_sys_wechat_share_detail ON t_sys_should_holiday.SHOULD_DATE = LEFT (\n" +
                "\tt_sys_wechat_share_detail.CREATE_TIME,\n" +
                "\t10\n" +
                ")\n" +
                "AND t_sys_wechat_share_detail.CREATE_USER_ID = '" + userId + "'\n" +
                "LEFT JOIN t_sys_wechat_share ON t_sys_wechat_share.SHARE_DATE = t_sys_should_holiday.SHOULD_DATE\n" +
                "AND t_sys_wechat_share.COMPANY_ID IN (\n" +
                "\t'" + userId + "'\n" +
                ")\n" +
                "LEFT JOIN t_wechat_friends_contacts ON LEFT (\n" +
                "\tt_wechat_friends_contacts.CREATE_TIME,\n" +
                "\t10\n" +
                ") = t_sys_should_holiday.SHOULD_DATE\n" +
                "AND t_wechat_friends_contacts.USER_ID = '" + userId + "'\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND LEFT (\n" +
                "\tt_sys_should_holiday.SHOULD_DATE,\n" +
                "\t7\n" +
                ") = '" + dateTime + "'\n" +
                "AND t_sys_should_holiday.FRONT_FLAG = 1\n" +
                "GROUP BY\n" +
                "\tt_sys_should_holiday.SHOULD_DATE\n" +
                "ORDER BY\n" +
                "\tt_sys_should_holiday.SHOULD_DATE DESC");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getWechatContact(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "left( t_wechat_friends_contacts.CREATE_TIME,10) as date,\n" +
                "t_wechat_friends_contacts.USER_ID as userId,\n" +
                "\n" +
                "IFNULL(\n" +
                "\t\tt_wechat_friends_contacts.CONTACTS,\n" +
                "\t\t0\n" +
                "\t) as contacts\n" +
                "FROM t_wechat_friends_contacts\n" +
                "WHERE t_wechat_friends_contacts.USER_ID='" + userId + "'\n" +
                "AND LEFT(t_wechat_friends_contacts.CREATE_TIME,7)='" + dateTime + "'\n" +
                "ORDER BY\n" +
                "\tCREATE_TIME DESC");
        return super.queryForJsonList(sql.toString());
    }

    public String getdate(int i) // //获取前后日期 i为正数 向后推迟i天，负数时向前提前i天
    {
        Date dat = null;
        Calendar cd = Calendar.getInstance();
        cd.add(Calendar.DATE, -i);
        dat = cd.getTime();
        SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
        return dformat.format(dat);
    }

    public JSONObject checkIsLeave(String userId, String shareDate) {
        //判断是否请假
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" ID FROM t_sys_leave WHERE ? BETWEEN t_sys_leave.BEGIN_TIME");
        sql.append(" AND t_sys_leave.END_TIME AND t_sys_leave.USER_ID =?");
        sql.append(" GROUP BY t_sys_leave.USER_ID");
        return super.queryForJsonObject(sql.toString(), shareDate, userId);
    }

    public List<JSONObject> getAllCustomerForRM(String userId, String type, StringBuffer condition, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND VISIT_MODE =" + type + "\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "GROUP BY t_wrt_customer_visit.customer_id\n");
        sql.append(condition);
        sql.append("ORDER BY t_wrt_customer_visit.ARRIVALS_TIME desc\n");
        super.appendPageSql(sql, requestPage, pageSize);

        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getNewCustomerForRM(String userId, String dateTime, String type, StringBuffer condition, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND VISIT_MODE =" + type + "\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "GROUP BY t_wrt_customer_visit.customer_id \n" +
                "HAVING t_wrt_customer_visit.customer_id in (SELECT\n" +
                "\tt3.needId\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.createUserId,\n" +
                "\t\t\tt1.customerId as needId,\n" +
                "\t\t\tt2.customerId\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE =" + type + "\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t\t'" + userId + "'\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\t\tAND LEFT (\n" +
                "\t\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t\t7\n" +
                "\t\t\t\t) = '" + dateTime + "'\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t) AS t1\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE =" + type + "\n" +
                "\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t'" + userId + "'\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\tAND LEFT (\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t7\n" +
                "\t\t\t) != '" + dateTime + "'\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t) AS t2 ON t1.createUserId = t2.createUserId\n" +
                "\t\tAND t1.customerId = t2.customerId\n" +
                "\t) AS t3\n" +
                "WHERE\n" +
                "\tt3.customerId IS NULL\n" +
                "GROUP BY t3.needId\n" +
                "\t)\n");
        sql.append(condition);
        sql.append("ORDER BY\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME DESC\n");
        super.appendPageSql(sql, requestPage, pageSize);

        return super.queryForJsonList(sql.toString());
    }

    public Integer getAllCustomerPageCount(String userId, String type, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from(\n" +
                "SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND VISIT_MODE =" + type + "\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "GROUP BY\n" +
                "\tt_wrt_customer_visit.customer_id\n");
        sql.append(condition);
        sql.append(") as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }

    public Integer getNewCustomerPageCount(String userId, String dateTime, String type, StringBuffer condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(1) as count from( SELECT\n" +
                "\tt_wrt_customer.`NAME` AS `customerName`,\n" +
                "\tt_wrt_customer.ID AS customerId,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWechat,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourageName,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_TYPE AS customerType,\n" +
                "\tt_wrt_customer_visit.CUSTOMER_CREDIT AS customerCredit,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\tt_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "\tt_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tIFNULL(\n" +
                "\t\tCEIL(\n" +
                "\t\t\tCEILING(\n" +
                "\t\t\t\tUNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t\t) - UNIX_TIMESTAMP(\n" +
                "\t\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t\t)\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS time\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t*\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_wrt_customer_visit`\n" +
                "\t\tWHERE\n" +
                "\t\t\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND t_wrt_customer_visit.VISIT_MODE =" + type + "\n" +
                "AND CUSTOMER_ID IS not NULL\n" +
                "\t\tORDER BY\n" +
                "\t\t\t`create_time` DESC\n" +
                "\t\t LIMIT 10000000000\n" +
                "\t) as t_wrt_customer_visit\n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID=t_wrt_customer_visit.CUSTOMER_ID\n" +
                "GROUP BY t_wrt_customer_visit.customer_id \n" +
                "HAVING t_wrt_customer_visit.customer_id in (SELECT\n" +
                "\tt3.needId\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt1.createUserId,\n" +
                "\t\t\tt1.customerId as needId,\n" +
                "\t\t\tt2.customerId\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE =" + type + "\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t\t'" + userId + "'\n" +
                "\t\t\t\t)\n" +
                "\t\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\t\tAND LEFT (\n" +
                "\t\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t\t7\n" +
                "\t\t\t\t) = '" + dateTime + "'\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t\t) AS t1\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE =" + type + "\n" +
                "\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID IN (\n" +
                "\t\t\t\t'" + userId + "'\n" +
                "\t\t\t)\n" +
                "\t\t\tAND t_wrt_customer_visit.CUSTOMER_ID IS NOT NULL\n" +
                "\t\t\tAND LEFT (\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\t7\n" +
                "\t\t\t) != '" + dateTime + "'\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\tt_wrt_customer_visit.CUSTOMER_ID\n" +
                "\t\t) AS t2 ON t1.createUserId = t2.createUserId\n" +
                "\t\tAND t1.customerId = t2.customerId\n" +
                "\t) AS t3\n" +
                "WHERE\n" +
                "\tt3.customerId IS NULL\n" +
                "GROUP BY t3.needId\n" +
                "\t)\n");
        sql.append(condition);
        sql.append("ORDER BY\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME DESC ) as num\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }
}
